import pymysql


# 封装数据库工具类

class DBUtil(object):
    # 添加类属性.
    conn = None
    cursor = None

    @classmethod
    def __get_conn(cls):
        # 判断 conn 是否为空, 如果是, 在创建.
        if DBUtil.conn is None:
            host = "*.*.*.*"
            port = 3306
            user = "***"
            password = "***"
            database = "***"

            # 1.创建数据库连接
            DBUtil.conn = pymysql.connect(host=host, port=port, user=user,
                                          password=password, database=database, charset="utf8")
        # 返回 非空连接
        return DBUtil.conn

    @classmethod
    def __close_conn(cls):
        # 判断, conn 不为空, 需要关闭.
        if cls.conn is not None:
            DBUtil.conn.close()
            cls.conn = None

    @classmethod
    # 常用方法: 查询一条
    def select_one(cls, sql):
        result = None
        cursor = None
        try:
            # 获取连接
            cls.conn = DBUtil.__get_conn()
            # 2.创建游标对象
            cursor = cls.conn.cursor()
            # 2.1 执行sql语句
            cursor.execute(sql)
            # 2,2 获取结果
            result = cursor.fetchone()
        except Exception as error:
            print("查询sql错误", str(error))
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            cls.__close_conn()
            # 将查询sql执行的结果, 返回.
            return result

    @classmethod
    # 常用方法: 增删改
    def uid_db(cls, sql):
        cursor = None
        try:
            # 获取连接
            cls.conn = DBUtil.__get_conn()
            # 2.创建游标对象
            cursor = cls.conn.cursor()
            # 2.1 执行sql语句
            cursor.execute(sql)
            print("影响的行数:", cls.conn.affected_rows())
            # 2.2 事务
            cls.conn.commit()

        except Exception as error:
            print("查询sql错误", str(error))
            # 回滚事务.
            cls.conn.rollback()
        finally:
            # 关闭游标
            cursor.close()
            # 关闭连接
            cls.__close_conn()


if __name__ == '__main__':
    res = DBUtil.select_one("select * from dept where id = 1")
    print("查询结果: ", res)
    DBUtil.uid_db("insert into dept value (7, '科技部')")
